package com.gosuncn.service.impl;

import com.gosuncn.pojo.OmsNodeInfo;
import com.gosuncn.service.OmsNodeJdbcService;
import com.gosuncn.tools.DbUtil;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/***
 *监控信息
 * @author zxl
 * @date 2021/7/26
*/
@Service
public class OmsNodeJdbcServiceImpl implements OmsNodeJdbcService {
//    @Autowired
//    JdbcTemplate jdbcTemplate;


    /**
     * 查询记录
     * @return
     */
    @Override
    public List<OmsNodeInfo> listData() {
        // 数据库连接
        Connection conn =  null;
        // 创建动态preparedStatement执行SQL语句
        PreparedStatement prep = null;
        // 结果集对象
        ResultSet rs = null;
        // 创建列表
        List<OmsNodeInfo> list = new ArrayList<>();
        try {
            // 获取数据库连接
            conn = DbUtil.getConnection();
            String sql = "select * from  tb_oms_node_info";
            // 预加载；创建一个动态prepareStatement实例实现SQL语句
            prep = conn.prepareStatement(sql);
            // 返回结果集对象
            rs = prep.executeQuery();
            // 遍历rs
            while (rs.next()) {
                // 创建oms_node_info实体类对象并将数据赋值
                OmsNodeInfo info = new OmsNodeInfo(rs.getInt("id"),rs.getString("name"),
                        rs.getInt("type_id"),rs.getInt("topo_visible"),
                        rs.getString("topo_ico"),rs.getInt("parent_id"),
                        rs.getDouble("topo_x"),rs.getDouble("topo_y"),
                        rs.getInt("online_status"),rs.getInt("alarm_status"),
                        rs.getString("org_code"),rs.getInt("area_id"),
                        rs.getInt("system_id"),rs.getInt("collect_type"),
                        rs.getString("collect_param"),rs.getString("zone_code"),
                        rs.getInt("second_type"),rs.getString("location"),
                        rs.getInt("sync_flag"),rs.getTime("collect_time"),
                        rs.getTime("create_time"),rs.getInt("monitor_enabled"),
                        rs.getInt("alarm_enabled"),rs.getString("extral_mark"),
                        rs.getInt("is_deleted"),rs.getString("collect_error"),
                        rs.getInt("error_collect_type"));
                // 添加到list列表
                list.add(info);
            }
        } catch (SQLException e) {
                e.printStackTrace();
        } finally {
                try {
                    // 关闭连接
                    DbUtil.close(rs,prep,conn);
                } catch (Exception e) {
                    e.printStackTrace();
                }
        }
        return list;
//        String sql = "select * from tb_oms_node_info";
//        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); //返回列表
    }

    /**
     * 添加记录
     * @param info
     * @return
     */
    @Override
    public int saveData(OmsNodeInfo info) {
        // 数据库连接
        Connection conn = null;
        // 创建动态preparedStatement执行SQL语句
        PreparedStatement prep = null;
        try {
            // 获取数据库连接
            conn = DbUtil.getConnection();
            // 关闭jdbc自动提交事务 改为手动进行事务回滚
            conn.setAutoCommit(false);

            String sql = "insert into tb_oms_node_info(name,type_id,topo_visible,topo_ico,parent_id,topo_x,topo_y," +
                    "online_status,alarm_status,org_code,area_id,system_id,collect_type,collect_param,zone_code," +
                    "second_type,location,sync_flag,collect_time,create_time,monitor_enabled,alarm_enabled,extral_mark," +
                    "is_deleted,collect_error,error_collect_type) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            prep = conn.prepareStatement(sql);
                // 对应sql语句中的占位符，按第一列开始
            // 列是从左到右编号的，并且从列1开始
            prep.setString(1,info.getName());
            prep.setInt(2,info.getTypeId());
            prep.setInt(3,info.getTopoVisible());
            prep.setString(4,info.getTopoIco());
            prep.setInt(5,info.getParentId());
            prep.setDouble(6,info.getTopoX());
            prep.setDouble(7,info.getTopoY());
            prep.setInt(8,info.getOnlineStatus());
            prep.setInt(9,info.getAlarmStatus());
            prep.setString(10,info.getOrgCode());
            prep.setInt(11,info.getAreaId());
            prep.setInt(12,info.getSystemId());
            prep.setInt(13,info.getCollectType());
            prep.setString(14,info.getCollectParam());
            prep.setString(15,info.getZoneCode() );
            prep.setInt(16,info.getSecondType());
            prep.setString(17,info.getLocation());
            prep.setInt(18,info.getSyncFlag());
            // setDate 方法使用的是 java.sql.Date 类型的日期参数 获取当前的时间戳 ;setTimestamp
            prep.setDate(19, new java.sql.Date(System.currentTimeMillis()));
            prep.setDate(20,new java.sql.Date(System.currentTimeMillis()));
            prep.setInt(21,info.getMonitorEnabled());
            prep.setInt(22,info.getAlarmEnabled());
            prep.setString(23,info.getExtralMark());
            prep.setInt(24,info.getDeleted());
            prep.setString(25,info.getCollectError());
            prep.setInt(26,info.getErrorCollectType());
            // 添加
            int i = prep.executeUpdate();
            // 提交事务
            conn.commit();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn!=null) {
                    // 在异常处理中回滚整个事务
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return 0;
        } finally {
            try {
                // 关闭连接
                DbUtil.close(null,prep,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        // try-catch语句中对可能出现的异常进行了处理，没有再手动throw异常，spring认为该方法成功执行
        // throw new RuntimeException();  因为Transactional事务管理会默认去找RuntimeException
//        String sql = "insert into tb_oms_node_info(name,type_id,topo_visible,topo_ico,parent_id,topo_x,topo_y,online_status,alarm_status,org_code,area_id,system_id,collect_type,collect_param,zone_code,second_type,location,sync_flag,collect_time,create_time,monitor_enabled,alarm_enabled,extral_mark,is_deleted,collect_error,error_collect_type) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//        jdbcTemplate.update(sql,info.getName(),info.getType_id(),info.getTopo_visible(),info.getTopo_ico(),info.getParent_id(),info.getTopo_x(),info.getTopo_y(),info.getOnline_status(),info.getAlarm_status(),info.getOrg_code()
//                ,info.getArea_id(),info.getSystem_id(),info.getCollect_type(),info.getCollect_param(),info.getZone_code(),info.getSecond_type(),info.getLocation(),info.getSync_flag(),new Date(),new Date(),info.getMonitor_enabled(),info.getAlarm_enabled(),info.getExtral_mark(),info.getIs_deleted(),info.getCollect_error(),info.getError_collect_type());
    }

    /**
     * 删除记录
     * @param id
     * @return
     */
    @Override
    public int deleteData(int id) {
        // 数据库连接
        Connection conn = null;
        // 创建动态preparedStatement执行SQL语句
        PreparedStatement prep = null;
        try {
            // 获取连接
            conn = DbUtil.getConnection();
            // 关闭jdbc自动提交事务 改为手动进行事务回滚
            conn.setAutoCommit(false);
            String sql ="delete from tb_oms_node_info where id =?";
            // 创建prepareStatement实例实现SQL语句
            prep = conn.prepareStatement(sql);
            // 将id赋值给第一列
            prep.setInt(1,id);
            // 删除
            int i = prep.executeUpdate();
            // 提交事务
            conn.commit();
            return  i;

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn!=null){
                    // 在异常处理中回滚整个事务
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return 0;
        } finally {
            try {
                // 关闭连接
                DbUtil.close(null,prep,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        jdbcTemplate.update(sql,id);
    }


    /**
     * 更新记录
     * @param info
     * @return
     */
    @Override
    public int updateData(OmsNodeInfo info)  {
        // 数据库连接
        Connection conn = null;
        // 创建动态preparedStatement执行SQL语句
        PreparedStatement prep = null;
        try {
            // 获取连接
            conn = DbUtil.getConnection();
            // 关闭jdbc自动提交事务 改为手动进行事务回滚
            conn.setAutoCommit(false);
            String sql = "update tb_oms_node_info set name=?,type_id=?,topo_visible=?,topo_ico=?,parent_id=?,topo_x=?," +
                    "topo_y=?,online_status=?,alarm_status=?,org_code=?,area_id=?,system_id=?,collect_type=?,collect_param=?," +
                    "zone_code=?,second_type=?,location=?,sync_flag=?,collect_time=?,create_time=?,monitor_enabled=?," +
                    "alarm_enabled=?,extral_mark=?,is_deleted=?,collect_error=?,error_collect_type=? where id=?";
            prep = conn.prepareStatement(sql);
            // 对应sql语句中的占位符，按第一列开始
            // 列是从左到右编号的，并且从列1开始
            prep.setString(1,info.getName());
            prep.setInt(2,info.getTypeId());
            prep.setInt(3,info.getTopoVisible());
            prep.setString(4,info.getTopoIco());
            prep.setInt(5,info.getParentId());
            prep.setDouble(6,info.getTopoX());
            prep.setDouble(7,info.getTopoY());
            prep.setInt(8,info.getOnlineStatus());
            prep.setInt(9,info.getAlarmStatus());
            prep.setString(10,info.getOrgCode());
            prep.setInt(11,info.getAreaId());
            prep.setInt(12,info.getSystemId());
            prep.setInt(13,info.getCollectType());
            prep.setString(14,info.getCollectParam());
            prep.setString(15,info.getZoneCode() );
            prep.setInt(16,info.getSecondType());
            prep.setString(17,info.getLocation());
            prep.setInt(18,info.getSyncFlag());
            // setDate 方法使用的是 java.sql.Date 类型的日期参数 获取当前的时间戳
            prep.setDate(19, new java.sql.Date(System.currentTimeMillis()));
            prep.setDate(20,new java.sql.Date(System.currentTimeMillis()));
            prep.setInt(21,info.getMonitorEnabled());
            prep.setInt(22,info.getAlarmEnabled());
            prep.setString(23,info.getExtralMark());
            prep.setInt(24,info.getDeleted());
            prep.setString(25,info.getCollectError());
            prep.setInt(26,info.getErrorCollectType());
            prep.setInt(27,info.getId());
            //更新
            int i = prep.executeUpdate();
            // 提交事务
            conn.commit();

            return i;

        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            try {
                // 关闭连接
                DbUtil.close(null,prep,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


//        Object[] objects = new Object[3]; //创建数组集合
//        objects[0]="zhangxiaolong";  //赋值
//        objects[1]="/zhang";
//        objects[2]=id;
//        info.setCollect_time(new Date());   //赋值
//        info.setCreate_time(new Date());
//        jdbcTemplate.update(sql,info.getName(),info.getType_id(),info.getTopo_visible(),info.getTopo_ico(),info.getParent_id(),info.getTopo_x(),info.getTopo_y(),info.getOnline_status(),info.getAlarm_status(),info.getOrg_code()
//                ,info.getArea_id(),info.getSystem_id(),info.getCollect_type(),info.getCollect_param(),info.getZone_code(),info.getSecond_type(),info.getLocation(),info.getSync_flag(),new Date(),new Date(),info.getMonitor_enabled(),info.getAlarm_enabled(),info.getExtral_mark(),info.getIs_deleted(),info.getCollect_error(),info.getError_collect_type(),info.getId());
//    }
    }

}
